//
//  FangDangDBTool.m
//  SqliteDemo
//
//  Created by 陈贵邦 on 15/10/16.
//  Copyright © 2015年 陈贵邦. All rights reserved.
//

#import "FangDangDBTool.h"
#define kUserTableName @"FanDangUser"
@implementation FangDangDBTool


- (id) init {
    self = [super init];
    if (self) {
        //========== 首先查看有没有建立message的数据库，如果未建立，则建立数据库=========
        _db = [SDBManager defaultDBManager].dataBase;
        
    }
    return self;
}

/**
 * @brief 创建数据库
 */
- (void) createDataBase {
    FMResultSet * set = [_db executeQuery:[NSString stringWithFormat:@"select count(*) from sqlite_master where type ='table' and name = '%@'",kUserTableName]];
    
    [set next];
    
    NSInteger count = [set intForColumnIndex:0];
    
    BOOL existTable = !!count;
    
    if (existTable) {
        // TODO:是否更新数据库
//        [AppDelegate showStatusWithText:@"数据库已经存在" duration:2];
    } else {
        // TODO: 插入新的数据库
        NSString * sql = @"CREATE TABLE FanDangUser (uid INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL , name VARCHAR(50) , idnumber VARCHAR(50) ,netnum VARCHAR(50),address VARCHAR(100),remark VARCHAR(100) ,isfan VARCHAR(100))";
        BOOL res = [_db executeUpdate:sql];
        if (!res) {
//            [AppDelegate showStatusWithText:@"数据库创建失败" duration:2];
            NSLog(@"数据库创建失败");
        } else {
//            [AppDelegate showStatusWithText:@"数据库创建成功" duration:2];
            NSLog(@"数据库创建成功");
            
        }
    }
}

/**
 * @brief 保存一条用户记录
 *
 * @param user 需要保存的用户数据
 */
- (BOOL) saveUser:(FanDangUser *) user {
    NSMutableString * query = [NSMutableString stringWithFormat:@"INSERT INTO FanDangUser"];
    NSMutableString * keys = [NSMutableString stringWithFormat:@" ("];
    NSMutableString * values = [NSMutableString stringWithFormat:@" ( "];
    NSMutableArray * arguments = [NSMutableArray arrayWithCapacity:5];
    if (user.name) {
        [keys appendString:@"name,"];
        [values appendString:@"?,"];
        [arguments addObject:user.name];
    }
    if (user.idnumber) {
        [keys appendString:@"idnumber,"];
        [values appendString:@"?,"];
        [arguments addObject:user.idnumber];
    }
    if (user.netnum) {
        [keys appendString:@"netnum,"];
        [values appendString:@"?,"];
        [arguments addObject:user.netnum];
    }
    if (user.address) {
        [keys appendString:@"address,"];
        [values appendString:@"?,"];
        [arguments addObject:user.address];
    }
    if (user.isfan) {
        [keys appendString:@"isfan,"];
        [values appendString:@"?,"];
        [arguments addObject:user.isfan];
    }
    
    if (user.remark) {
        [keys appendString:@"remark,"];
        [values appendString:@"?,"];
        [arguments addObject:user.remark];
    }
    
    
    
    [keys appendString:@")"];
    [values appendString:@")"];
    [query appendFormat:@" %@ VALUES%@",
     [keys stringByReplacingOccurrencesOfString:@",)" withString:@")"],
     [values stringByReplacingOccurrencesOfString:@",)" withString:@")"]];
    NSLog(@"%@",query);
//    [AppDelegate showStatusWithText:@"插入一条数据" duration:2.0];
    
   BOOL isSaveSuccess =  [_db executeUpdate:query withArgumentsInArray:arguments];
    return isSaveSuccess;
    
}

/**
 * @brief 删除一条用户数据
 *
 * @param uid 需要删除的用户的id
 */
- (void) deleteUserWithId:(NSString *) uid {
    NSString * query = [NSString stringWithFormat:@"DELETE FROM FanDangUser WHERE uid = '%@'",uid];
//    [AppDelegate showStatusWithText:@"删除一条数据" duration:2.0];
    [_db executeUpdate:query];
}

/**
 * @brief 修改用户的信息
 *
 * @param user 需要修改的用户信息
 */
- (BOOL) mergeWithUser:(FanDangUser *) user {
    if (!user.uid) {
        return NO;
    }
    NSString * query = @"UPDATE FanDangUser SET";
    NSMutableString * temp = [NSMutableString stringWithCapacity:20];
    // xxx = xxx;
    if (user.name) {
        [temp appendFormat:@" name = '%@',",user.name];
    }
    if (user.idnumber) {
        [temp appendFormat:@" idnumber = '%@',",user.idnumber];
    }
    if (user.netnum) {
        [temp appendFormat:@" netnum = '%@',",user.netnum];
    }
    if (user.address) {
        [temp appendFormat:@" address = '%@',",user.address];
    }
    if (user.isfan) {
        [temp appendFormat:@" isfan = '%@',",user.isfan];
    }
    if (user.remark) {
        [temp appendFormat:@" remark = '%@',",user.remark];
    }
    [temp appendString:@")"];
    query = [query stringByAppendingFormat:@"%@ WHERE uid = '%@'",[temp stringByReplacingOccurrencesOfString:@",)" withString:@""],user.uid];
    NSLog(@"%@",query);
    
//    [AppDelegate showStatusWithText:@"修改一条数据" duration:2.0];
   BOOL isupdateSuccess =  [_db executeUpdate:query];
    return isupdateSuccess;
}

/**
 * @brief 模拟分页查找数据。取uid大于某个值以后的limit个数据
 *
 * @param uid
 * @param limit 每页取多少个
 */
- (NSArray *) findWithUid:(NSString *) uid limit:(int) limit {
    NSString * query = @"SELECT uid,name,idnumber,netnum,address,remark,isfan FROM FanDangUser";
    if (!uid) {
        if (limit !=0) {
            query = [query stringByAppendingFormat:@" ORDER BY uid DESC limit %d",limit];
        }else{
            query = [query stringByAppendingFormat:@" ORDER BY uid DESC"];
        }
        
    } else {
        
        if (limit !=0) {
            query = [query stringByAppendingFormat:@" WHERE uid > %@ ORDER BY uid DESC limit %d",uid,limit];
        }else{
            query = [query stringByAppendingFormat:@" WHERE uid > %@ ORDER BY uid DESC",uid];
        }
    }
    
    FMResultSet * rs = [_db executeQuery:query];
    NSMutableArray * array = [NSMutableArray arrayWithCapacity:[rs columnCount]];
    while ([rs next]) {
        FanDangUser * user = [FanDangUser new];
        user.uid = [rs stringForColumn:@"uid"];
        user.name = [rs stringForColumn:@"name"];
        user.idnumber = [rs stringForColumn:@"idnumber"];
        user.netnum = [rs stringForColumn:@"netnum"];
        user.address = [rs stringForColumn:@"address"];
        user.remark = [rs stringForColumn:@"remark"];
        user.isfan = [rs stringForColumn:@"isfan"];
        
        [array addObject:user];
    }
    [rs close];
    return array;
}


@end
